using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using RaisingStudio.Data.Entities;
using IBatisNet.DataMapper;

namespace PerformanceTest
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private static void ShowResult(int count, List<long> timing, string title)
        {
            string message = string.Format("insert: {0}.\nselect: {1}.\nupdate: {2}.\ndelete: {3}.\n", timing[0], timing[1], timing[2], timing[3]);
            string caption = string.Format("{0}: {0}", title, count);
            MessageBox.Show(message, caption, MessageBoxButtons.OK);
        }

        private void button1_Click(object sender, EventArgs e)
        {
            int count = int.Parse(this.comboBox1.Text);
            List<long> timing = new List<long>();
            Test_Command(count, timing);
            string title = "command";
            ShowResult(count, timing, title);
        }

        private static void Test_Command(int count, List<long> timing)
        {
            RaisingStudio.Data.Providers.DataContext dc = new RaisingStudio.Data.Providers.DataContext();
            if (dc.DatabaseType == RaisingStudio.Data.DatabaseType.SQL)
            {
                Test_SqlServer_Command(count, timing, dc.Connection.ConnectionString);
            }
            else
            {
                // TODO: oracle.
                if (dc.DatabaseType == RaisingStudio.Data.DatabaseType.ORACLE)
                {
                    Test_SystemOracle_Command(count, timing, dc.Connection.ConnectionString);
                }
                else
                {
                    Test_Oracle_Command(count, timing, dc.Connection.ConnectionString);
                }
            }
        }

        private static void Test_SqlServer_Command(int count, List<long> timing, string connectionString)
        {
            System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString);
            connection.Open();
            System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
            sw.Start();

            System.Data.SqlClient.SqlCommand insertCommand = connection.CreateCommand();
            insertCommand.CommandText = "INSERT INTO DAC_USER (USER_ID, USERNAME, FULLNAME, USER_PWD, CHANGINGPWD, USERLOCKED, LASTLOGIN, USER_DESC) VALUES (@UserID, @UserName, @FullName, @UserPwd, @ChangingPwd, @UserLocked, @LastLogin, @UserDesc)";

            System.Data.SqlClient.SqlParameter pUserID = insertCommand.CreateParameter();
            pUserID.ParameterName = "@UserID";
            pUserID.DbType = DbType.String;
            insertCommand.Parameters.Add(pUserID);

            System.Data.SqlClient.SqlParameter pUserName = insertCommand.CreateParameter();
            pUserName.ParameterName = "@UserName";
            pUserName.DbType = DbType.String;
            insertCommand.Parameters.Add(pUserName);

            System.Data.SqlClient.SqlParameter pFullName = insertCommand.CreateParameter();
            pFullName.ParameterName = "@FullName";
            pFullName.DbType = DbType.String;
            insertCommand.Parameters.Add(pFullName);

            System.Data.SqlClient.SqlParameter pUserPwd = insertCommand.CreateParameter();
            pUserPwd.ParameterName = "@UserPwd";
            pUserPwd.DbType = DbType.String;
            insertCommand.Parameters.Add(pUserPwd);

            System.Data.SqlClient.SqlParameter pChangingPwd = insertCommand.CreateParameter();
            pChangingPwd.ParameterName = "@ChangingPwd";
            pChangingPwd.DbType = DbType.Int32;
            insertCommand.Parameters.Add(pChangingPwd);

            System.Data.SqlClient.SqlParameter pUserLocked = insertCommand.CreateParameter();
            pUserLocked.ParameterName = "@UserLocked";
            pUserLocked.DbType = DbType.Int32;
            insertCommand.Parameters.Add(pUserLocked);

            System.Data.SqlClient.SqlParameter pLastLogin = insertCommand.CreateParameter();
            pLastLogin.ParameterName = "@LastLogin";
            pLastLogin.DbType = DbType.DateTime;
            insertCommand.Parameters.Add(pLastLogin);

            System.Data.SqlClient.SqlParameter pUserDesc = insertCommand.CreateParameter();
            pUserDesc.ParameterName = "@UserDesc";
            pUserDesc.DbType = DbType.String;
            insertCommand.Parameters.Add(pUserDesc);

            for (int i = 0; i < count; i++)
            {
                pUserID.Value = i.ToString();
                pUserName.Value = string.Format("User{0}", i);
                pFullName.Value = string.Format("User full name: {0}", i);
                pUserPwd.Value = System.DBNull.Value;
                pChangingPwd.Value = 0;
                pUserLocked.Value = 0;
                pLastLogin.Value = System.DBNull.Value;
                pUserDesc.Value = System.DBNull.Value;
                insertCommand.ExecuteNonQuery();
            }

            sw.Stop();
            timing.Add(sw.ElapsedMilliseconds);
            System.Diagnostics.Debug.WriteLine(string.Format("Insert User by command count: {0} in {1}.", count, sw.ElapsedMilliseconds));
            sw.Reset();

            sw.Start();

            System.Data.SqlClient.SqlCommand selectCommand = connection.CreateCommand();
            selectCommand.CommandText = "SELECT USER_ID, USERNAME, FULLNAME, USER_PWD, CHANGINGPWD, USERLOCKED, LASTLOGIN, USER_DESC FROM DAC_USER";
            System.Data.SqlClient.SqlDataReader reader = selectCommand.ExecuteReader();
            IList<SystemUser> users = new List<SystemUser>();
            while (reader.Read())
            {
                SystemUser user = new SystemUser();
                user.UserID = reader.GetString(0);
                user.UserName = reader.GetString(1);
                user.FullName = reader.IsDBNull(2) ? null : reader.GetString(2);
                user.Password = reader.IsDBNull(3) ? null : reader.GetString(3);
                if (reader.IsDBNull(4))
                {
                    user.ChangingPassword = null;
                }
                else
                {
                    user.ChangingPassword = (reader.GetInt32(4) > 0 ? true : false);
                }
                if (reader.IsDBNull(5))
                {
                    user.UserLocked = null;
                }
                else
                {
                    user.UserLocked = (reader.GetInt32(5) > 0 ? true : false);
                }
                if (reader.IsDBNull(6))
                {
                    user.LastLogin = null;
                }
                else
                {
                    user.LastLogin = reader.GetDateTime(6);
                }
                user.UserDescription = reader.IsDBNull(7) ? null : reader.GetString(7);
                users.Add(user);
            }
            reader.Close();

            sw.Stop();
            timing.Add(sw.ElapsedMilliseconds);
            System.Diagnostics.Debug.WriteLine(string.Format("Query User by command count: {0} in {1}.", count, sw.ElapsedMilliseconds));
            sw.Reset();

            sw.Start();

            System.Data.SqlClient.SqlCommand updateCommand = connection.CreateCommand();
            updateCommand.CommandText = "UPDATE DAC_USER SET USERNAME = @UserName, FULLNAME = @FullName, USER_PWD = @UserPwd, CHANGINGPWD = @ChangingPwd, USERLOCKED = @UserLocked, LASTLOGIN = @LastLogin, USER_DESC = @UserDesc WHERE USER_ID = @UserID";

            pUserID = updateCommand.CreateParameter();
            pUserID.ParameterName = "@UserID";
            pUserID.DbType = DbType.String;
            updateCommand.Parameters.Add(pUserID);

            pUserName = updateCommand.CreateParameter();
            pUserName.ParameterName = "@UserName";
            pUserName.DbType = DbType.String;
            updateCommand.Parameters.Add(pUserName);

            pFullName = updateCommand.CreateParameter();
            pFullName.ParameterName = "@FullName";
            pFullName.DbType = DbType.String;
            updateCommand.Parameters.Add(pFullName);

            pUserPwd = updateCommand.CreateParameter();
            pUserPwd.ParameterName = "@UserPwd";
            pUserPwd.DbType = DbType.String;
            updateCommand.Parameters.Add(pUserPwd);

            pChangingPwd = updateCommand.CreateParameter();
            pChangingPwd.ParameterName = "@ChangingPwd";
            pChangingPwd.DbType = DbType.Int32;
            updateCommand.Parameters.Add(pChangingPwd);


            pUserLocked = updateCommand.CreateParameter();
            pUserLocked.ParameterName = "@UserLocked";
            pUserLocked.DbType = DbType.Int32;
            updateCommand.Parameters.Add(pUserLocked);

            pLastLogin = updateCommand.CreateParameter();
            pLastLogin.ParameterName = "@LastLogin";
            pLastLogin.DbType = DbType.DateTime;
            updateCommand.Parameters.Add(pLastLogin);

            pUserDesc = updateCommand.CreateParameter();
            pUserDesc.ParameterName = "@UserDesc";
            pUserDesc.DbType = DbType.String;
            updateCommand.Parameters.Add(pUserDesc);

            foreach (SystemUser user in users)
            {
                pUserID.Value = user.UserID;
                pUserName.Value = user.UserName;
                pUserPwd.Value = "new password";
                pLastLogin.Value = System.DateTime.Now;

                if (user.FullName == null)
                {
                    pFullName.Value = System.DBNull.Value;
                }
                else
                {
                    pFullName.Value = user.FullName;
                }
                if (user.ChangingPassword.HasValue)
                {
                    pChangingPwd.Value = user.ChangingPassword.Value;
                }
                else
                {
                    pChangingPwd.Value = System.DBNull.Value;
                }
                if (user.UserLocked.HasValue)
                {
                    pUserLocked.Value = user.UserLocked.Value;
                }
                else
                {
                    pUserLocked.Value = System.DBNull.Value;
                }
                //if (user.LastLogin.HasValue)
                //{
                //    pLastLogin.Value = user.LastLogin.Value;
                //}
                //else
                //{
                //    pLastLogin.Value = System.DBNull.Value;
                //}
                if (user.UserDescription == null)
                {
                    pUserDesc.Value = System.DBNull.Value;
                }
                else
                {
                    pUserDesc.Value = user.UserDescription;
                }
                updateCommand.ExecuteNonQuery();
            }
            sw.Stop();
            timing.Add(sw.ElapsedMilliseconds);
            System.Diagnostics.Debug.WriteLine(string.Format("Update User by command count: {0} in {1}.", count, sw.ElapsedMilliseconds));
            sw.Reset();

            sw.Start();

            System.Data.SqlClient.SqlCommand deleteCommand = connection.CreateCommand();
            deleteCommand.CommandText = "DELETE FROM DAC_USER WHERE USER_ID = @UserID";

            pUserID = deleteCommand.CreateParameter();
            pUserID.ParameterName = "@UserID";
            pUserID.DbType = DbType.String;
            deleteCommand.Parameters.Add(pUserID);

            foreach (SystemUser user in users)
            {
                pUserID.Value = user.UserID;
                deleteCommand.ExecuteNonQuery();
            }

            sw.Stop();
            timing.Add(sw.ElapsedMilliseconds);
            System.Diagnostics.Debug.WriteLine(string.Format("Delete User by command count: {0} in {1}.", count, sw.ElapsedMilliseconds));
            connection.Close();
        }

        private static void Test_Oracle_Command(int count, List<long> timing, string connectionString)
        {
            Oracle.DataAccess.Client.OracleConnection connection = new Oracle.DataAccess.Client.OracleConnection(connectionString);
            connection.Open();
            System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
            sw.Start();

            Oracle.DataAccess.Client.OracleCommand insertCommand = connection.CreateCommand();
            insertCommand.CommandText = "INSERT INTO DAC_USER (USER_ID, USERNAME, FULLNAME, USER_PWD, CHANGINGPWD, USERLOCKED, LASTLOGIN, USER_DESC) VALUES (:UserID, :UserName, :FullName, :UserPwd, :ChangingPwd, :UserLocked, :LastLogin, :UserDesc)";

            Oracle.DataAccess.Client.OracleParameter pUserID = insertCommand.CreateParameter();
            pUserID.ParameterName = ":UserID";
            pUserID.DbType = DbType.String;
            insertCommand.Parameters.Add(pUserID);

            Oracle.DataAccess.Client.OracleParameter pUserName = insertCommand.CreateParameter();
            pUserName.ParameterName = ":UserName";
            pUserName.DbType = DbType.String;
            insertCommand.Parameters.Add(pUserName);

            Oracle.DataAccess.Client.OracleParameter pFullName = insertCommand.CreateParameter();
            pFullName.ParameterName = ":FullName";
            pFullName.DbType = DbType.String;
            insertCommand.Parameters.Add(pFullName);

            Oracle.DataAccess.Client.OracleParameter pUserPwd = insertCommand.CreateParameter();
            pUserPwd.ParameterName = ":UserPwd";
            pUserPwd.DbType = DbType.String;
            insertCommand.Parameters.Add(pUserPwd);

            Oracle.DataAccess.Client.OracleParameter pChangingPwd = insertCommand.CreateParameter();
            pChangingPwd.ParameterName = ":ChangingPwd";
            pChangingPwd.DbType = DbType.Int32;
            insertCommand.Parameters.Add(pChangingPwd);

            Oracle.DataAccess.Client.OracleParameter pUserLocked = insertCommand.CreateParameter();
            pUserLocked.ParameterName = ":UserLocked";
            pUserLocked.DbType = DbType.Int32;
            insertCommand.Parameters.Add(pUserLocked);

            Oracle.DataAccess.Client.OracleParameter pLastLogin = insertCommand.CreateParameter();
            pLastLogin.ParameterName = ":LastLogin";
            pLastLogin.DbType = DbType.DateTime;
            insertCommand.Parameters.Add(pLastLogin);

            Oracle.DataAccess.Client.OracleParameter pUserDesc = insertCommand.CreateParameter();
            pUserDesc.ParameterName = ":UserDesc";
            pUserDesc.DbType = DbType.String;
            insertCommand.Parameters.Add(pUserDesc);

            for (int i = 0; i < count; i++)
            {
                pUserID.Value = i.ToString();
                pUserName.Value = string.Format("User{0}", i);
                pFullName.Value = string.Format("User full name: {0}", i);
                pUserPwd.Value = System.DBNull.Value;
                pChangingPwd.Value = 0;
                pUserLocked.Value = 0;
                pLastLogin.Value = System.DBNull.Value;
                pUserDesc.Value = System.DBNull.Value;
                insertCommand.ExecuteNonQuery();
            }

            sw.Stop();
            timing.Add(sw.ElapsedMilliseconds);
            System.Diagnostics.Debug.WriteLine(string.Format("Insert User by command count: {0} in {1}.", count, sw.ElapsedMilliseconds));
            sw.Reset();

            sw.Start();

            Oracle.DataAccess.Client.OracleCommand selectCommand = connection.CreateCommand();
            selectCommand.CommandText = "SELECT USER_ID, USERNAME, FULLNAME, USER_PWD, CHANGINGPWD, USERLOCKED, LASTLOGIN, USER_DESC FROM DAC_USER";
            Oracle.DataAccess.Client.OracleDataReader reader = selectCommand.ExecuteReader();
            IList<SystemUser> users = new List<SystemUser>();
            while (reader.Read())
            {
                SystemUser user = new SystemUser();
                user.UserID = reader.GetString(0);
                user.UserName = reader.GetString(1);
                user.FullName = reader.IsDBNull(2) ? null : reader.GetString(2);
                user.Password = reader.IsDBNull(3) ? null : reader.GetString(3);
                if (reader.IsDBNull(4))
                {
                    user.ChangingPassword = null;
                }
                else
                {
                    user.ChangingPassword = (Convert.ToInt32(reader.GetValue(4)) > 0 ? true : false);
                }
                if (reader.IsDBNull(5))
                {
                    user.UserLocked = null;
                }
                else
                {
                    user.UserLocked = (Convert.ToInt32(reader.GetValue(5)) > 0 ? true : false);
                }
                if (reader.IsDBNull(6))
                {
                    user.LastLogin = null;
                }
                else
                {
                    user.LastLogin = reader.GetDateTime(6);
                }
                user.UserDescription = reader.IsDBNull(7) ? null : reader.GetString(7);
                users.Add(user);
            }
            reader.Close();
            
            // reopen it.
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            
            sw.Stop();
            timing.Add(sw.ElapsedMilliseconds);
            System.Diagnostics.Debug.WriteLine(string.Format("Query User by command count: {0} in {1}.", count, sw.ElapsedMilliseconds));
            sw.Reset();

            sw.Start();

            Oracle.DataAccess.Client.OracleCommand updateCommand = connection.CreateCommand();
            updateCommand.CommandText = "UPDATE DAC_USER SET USERNAME = :UserName, FULLNAME = :FullName, USER_PWD = :UserPwd, CHANGINGPWD = :ChangingPwd, USERLOCKED = :UserLocked, LASTLOGIN = TO_DATE(:LastLogin, 'YYYY-MM-DD HH:MI:SS'), USER_DESC = :UserDesc WHERE USER_ID = :UserID";

            pUserID = updateCommand.CreateParameter();
            pUserID.ParameterName = ":UserID";
            pUserID.DbType = DbType.String;
            updateCommand.Parameters.Add(pUserID);

            pUserName = updateCommand.CreateParameter();
            pUserName.ParameterName = ":UserName";
            pUserName.DbType = DbType.String;
            updateCommand.Parameters.Add(pUserName);

            pFullName = updateCommand.CreateParameter();
            pFullName.ParameterName = ":FullName";
            pFullName.DbType = DbType.String;
            updateCommand.Parameters.Add(pFullName);

            pUserPwd = updateCommand.CreateParameter();
            pUserPwd.ParameterName = ":UserPwd";
            pUserPwd.DbType = DbType.String;
            updateCommand.Parameters.Add(pUserPwd);

            pChangingPwd = updateCommand.CreateParameter();
            pChangingPwd.ParameterName = ":ChangingPwd";
            pChangingPwd.DbType = DbType.Int32;
            updateCommand.Parameters.Add(pChangingPwd);


            pUserLocked = updateCommand.CreateParameter();
            pUserLocked.ParameterName = ":UserLocked";
            pUserLocked.DbType = DbType.Int32;
            updateCommand.Parameters.Add(pUserLocked);

            pLastLogin = updateCommand.CreateParameter();
            pLastLogin.ParameterName = ":LastLogin";
            //pLastLogin.DbType = DbType.DateTime;
            //pLastLogin.IsNullable = true;
            //pLastLogin.OracleDbType = Oracle.DataAccess.Client.OracleDbType.Date;
            updateCommand.Parameters.Add(pLastLogin);

            pUserDesc = updateCommand.CreateParameter();
            pUserDesc.ParameterName = ":UserDesc";
            pUserDesc.DbType = DbType.String;
            updateCommand.Parameters.Add(pUserDesc);

            foreach (SystemUser user in users)
            {
                pUserID.Value = user.UserID;
                pUserName.Value = user.UserName;
                pUserPwd.Value = "new password";
                pLastLogin.Value = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

                if (user.FullName == null)
                {
                    pFullName.Value = System.DBNull.Value;
                }
                else
                {
                    pFullName.Value = user.FullName;
                }
                if (user.ChangingPassword.HasValue)
                {
                    pChangingPwd.Value = user.ChangingPassword.Value ? 1 : 0;
                }
                else
                {
                    pChangingPwd.Value = System.DBNull.Value;
                }
                if (user.UserLocked.HasValue)
                {
                    pUserLocked.Value = user.UserLocked.Value ? 1 : 0;
                }
                else
                {
                    pUserLocked.Value = System.DBNull.Value;
                }
                //if (user.LastLogin.HasValue)
                //{
                //    pLastLogin.Value = user.LastLogin.Value;
                //}
                //else
                //{
                //    pLastLogin.Value = System.DBNull.Value;
                //}
                if (user.UserDescription == null)
                {
                    pUserDesc.Value = System.DBNull.Value;
                }
                else
                {
                    pUserDesc.Value = user.UserDescription;
                }
                updateCommand.ExecuteNonQuery();
            }
            sw.Stop();
            timing.Add(sw.ElapsedMilliseconds);
            System.Diagnostics.Debug.WriteLine(string.Format("Update User by command count: {0} in {1}.", count, sw.ElapsedMilliseconds));
            sw.Reset();

            sw.Start();

            Oracle.DataAccess.Client.OracleCommand deleteCommand = connection.CreateCommand();
            deleteCommand.CommandText = "DELETE FROM DAC_USER WHERE USER_ID = :UserID";

            pUserID = deleteCommand.CreateParameter();
            pUserID.ParameterName = ":UserID";
            pUserID.DbType = DbType.String;
            deleteCommand.Parameters.Add(pUserID);

            foreach (SystemUser user in users)
            {
                pUserID.Value = user.UserID;
                deleteCommand.ExecuteNonQuery();
            }

            sw.Stop();
            timing.Add(sw.ElapsedMilliseconds);
            System.Diagnostics.Debug.WriteLine(string.Format("Delete User by command count: {0} in {1}.", count, sw.ElapsedMilliseconds));
            connection.Close();
        }
        private static void Test_SystemOracle_Command(int count, List<long> timing, string connectionString)
        {
            System.Data.OracleClient.OracleConnection connection = new System.Data.OracleClient.OracleConnection(connectionString);
            connection.Open();
            System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
            sw.Start();

            System.Data.OracleClient.OracleCommand insertCommand = connection.CreateCommand();
            insertCommand.CommandText = "INSERT INTO DAC_USER (USER_ID, USERNAME, FULLNAME, USER_PWD, CHANGINGPWD, USERLOCKED, LASTLOGIN, USER_DESC) VALUES (:UserID, :UserName, :FullName, :UserPwd, :ChangingPwd, :UserLocked, :LastLogin, :UserDesc)";

            System.Data.OracleClient.OracleParameter pUserID = insertCommand.CreateParameter();
            pUserID.ParameterName = ":UserID";
            pUserID.DbType = DbType.String;
            insertCommand.Parameters.Add(pUserID);

            System.Data.OracleClient.OracleParameter pUserName = insertCommand.CreateParameter();
            pUserName.ParameterName = ":UserName";
            pUserName.DbType = DbType.String;
            insertCommand.Parameters.Add(pUserName);

            System.Data.OracleClient.OracleParameter pFullName = insertCommand.CreateParameter();
            pFullName.ParameterName = ":FullName";
            pFullName.DbType = DbType.String;
            insertCommand.Parameters.Add(pFullName);

            System.Data.OracleClient.OracleParameter pUserPwd = insertCommand.CreateParameter();
            pUserPwd.ParameterName = ":UserPwd";
            pUserPwd.DbType = DbType.String;
            insertCommand.Parameters.Add(pUserPwd);

            System.Data.OracleClient.OracleParameter pChangingPwd = insertCommand.CreateParameter();
            pChangingPwd.ParameterName = ":ChangingPwd";
            pChangingPwd.DbType = DbType.Int32;
            insertCommand.Parameters.Add(pChangingPwd);

            System.Data.OracleClient.OracleParameter pUserLocked = insertCommand.CreateParameter();
            pUserLocked.ParameterName = ":UserLocked";
            pUserLocked.DbType = DbType.Int32;
            insertCommand.Parameters.Add(pUserLocked);

            System.Data.OracleClient.OracleParameter pLastLogin = insertCommand.CreateParameter();
            pLastLogin.ParameterName = ":LastLogin";
            pLastLogin.DbType = DbType.DateTime;
            insertCommand.Parameters.Add(pLastLogin);

            System.Data.OracleClient.OracleParameter pUserDesc = insertCommand.CreateParameter();
            pUserDesc.ParameterName = ":UserDesc";
            pUserDesc.DbType = DbType.String;
            insertCommand.Parameters.Add(pUserDesc);

            for (int i = 0; i < count; i++)
            {
                pUserID.Value = i.ToString();
                pUserName.Value = string.Format("User{0}", i);
                pFullName.Value = string.Format("User full name: {0}", i);
                pUserPwd.Value = System.DBNull.Value;
                pChangingPwd.Value = 0;
                pUserLocked.Value = 0;
                pLastLogin.Value = System.DBNull.Value;
                pUserDesc.Value = System.DBNull.Value;
                insertCommand.ExecuteNonQuery();
            }

            sw.Stop();
            timing.Add(sw.ElapsedMilliseconds);
            System.Diagnostics.Debug.WriteLine(string.Format("Insert User by command count: {0} in {1}.", count, sw.ElapsedMilliseconds));
            sw.Reset();

            sw.Start();

            System.Data.OracleClient.OracleCommand selectCommand = connection.CreateCommand();
            selectCommand.CommandText = "SELECT USER_ID, USERNAME, FULLNAME, USER_PWD, CHANGINGPWD, USERLOCKED, LASTLOGIN, USER_DESC FROM DAC_USER";
            System.Data.OracleClient.OracleDataReader reader = selectCommand.ExecuteReader();
            IList<SystemUser> users = new List<SystemUser>();
            while (reader.Read())
            {
                SystemUser user = new SystemUser();
                user.UserID = reader.GetString(0);
                user.UserName = reader.GetString(1);
                user.FullName = reader.IsDBNull(2) ? null : reader.GetString(2);
                user.Password = reader.IsDBNull(3) ? null : reader.GetString(3);
                if (reader.IsDBNull(4))
                {
                    user.ChangingPassword = null;
                }
                else
                {
                    user.ChangingPassword = (Convert.ToInt32(reader.GetValue(4)) > 0 ? true : false);
                }
                if (reader.IsDBNull(5))
                {
                    user.UserLocked = null;
                }
                else
                {
                    user.UserLocked = (Convert.ToInt32(reader.GetValue(5)) > 0 ? true : false);
                }
                if (reader.IsDBNull(6))
                {
                    user.LastLogin = null;
                }
                else
                {
                    user.LastLogin = reader.GetDateTime(6);
                }
                user.UserDescription = reader.IsDBNull(7) ? null : reader.GetString(7);
                users.Add(user);
            }
            reader.Close();

            // reopen it.
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            sw.Stop();
            timing.Add(sw.ElapsedMilliseconds);
            System.Diagnostics.Debug.WriteLine(string.Format("Query User by command count: {0} in {1}.", count, sw.ElapsedMilliseconds));
            sw.Reset();

            sw.Start();

            System.Data.OracleClient.OracleCommand updateCommand = connection.CreateCommand();
            updateCommand.CommandText = "UPDATE DAC_USER SET USERNAME = :UserName, FULLNAME = :FullName, USER_PWD = :UserPwd, CHANGINGPWD = :ChangingPwd, USERLOCKED = :UserLocked, LASTLOGIN = :LastLogin, USER_DESC = :UserDesc WHERE USER_ID = :UserID";

            pUserID = updateCommand.CreateParameter();
            pUserID.ParameterName = ":UserID";
            pUserID.DbType = DbType.String;
            updateCommand.Parameters.Add(pUserID);

            pUserName = updateCommand.CreateParameter();
            pUserName.ParameterName = ":UserName";
            pUserName.DbType = DbType.String;
            updateCommand.Parameters.Add(pUserName);

            pFullName = updateCommand.CreateParameter();
            pFullName.ParameterName = ":FullName";
            pFullName.DbType = DbType.String;
            updateCommand.Parameters.Add(pFullName);

            pUserPwd = updateCommand.CreateParameter();
            pUserPwd.ParameterName = ":UserPwd";
            pUserPwd.DbType = DbType.String;
            updateCommand.Parameters.Add(pUserPwd);

            pChangingPwd = updateCommand.CreateParameter();
            pChangingPwd.ParameterName = ":ChangingPwd";
            pChangingPwd.DbType = DbType.Int32;
            updateCommand.Parameters.Add(pChangingPwd);


            pUserLocked = updateCommand.CreateParameter();
            pUserLocked.ParameterName = ":UserLocked";
            pUserLocked.DbType = DbType.Int32;
            updateCommand.Parameters.Add(pUserLocked);

            pLastLogin = updateCommand.CreateParameter();
            pLastLogin.ParameterName = ":LastLogin";
            pLastLogin.DbType = DbType.DateTime;
            updateCommand.Parameters.Add(pLastLogin);

            pUserDesc = updateCommand.CreateParameter();
            pUserDesc.ParameterName = ":UserDesc";
            pUserDesc.DbType = DbType.String;
            updateCommand.Parameters.Add(pUserDesc);

            foreach (SystemUser user in users)
            {
                pUserID.Value = user.UserID;
                pUserName.Value = user.UserName;
                pUserPwd.Value = "new password";
                pLastLogin.Value = System.DateTime.Now;

                if (user.FullName == null)
                {
                    pFullName.Value = System.DBNull.Value;
                }
                else
                {
                    pFullName.Value = user.FullName;
                }
                if (user.ChangingPassword.HasValue)
                {
                    pChangingPwd.Value = user.ChangingPassword.Value;// ? 1 : 0;
                }
                else
                {
                    pChangingPwd.Value = System.DBNull.Value;
                }
                if (user.UserLocked.HasValue)
                {
                    pUserLocked.Value = user.UserLocked.Value;// ? 1 : 0;
                }
                else
                {
                    pUserLocked.Value = System.DBNull.Value;
                }
                //if (user.LastLogin.HasValue)
                //{
                //    pLastLogin.Value = user.LastLogin.Value;
                //}
                //else
                //{
                //    pLastLogin.Value = System.DBNull.Value;
                //}
                if (user.UserDescription == null)
                {
                    pUserDesc.Value = System.DBNull.Value;
                }
                else
                {
                    pUserDesc.Value = user.UserDescription;
                }
                updateCommand.ExecuteNonQuery();
            }
            sw.Stop();
            timing.Add(sw.ElapsedMilliseconds);
            System.Diagnostics.Debug.WriteLine(string.Format("Update User by command count: {0} in {1}.", count, sw.ElapsedMilliseconds));
            sw.Reset();

            sw.Start();

            System.Data.OracleClient.OracleCommand deleteCommand = connection.CreateCommand();
            deleteCommand.CommandText = "DELETE FROM DAC_USER WHERE USER_ID = :UserID";

            pUserID = deleteCommand.CreateParameter();
            pUserID.ParameterName = ":UserID";
            pUserID.DbType = DbType.String;
            deleteCommand.Parameters.Add(pUserID);

            foreach (SystemUser user in users)
            {
                pUserID.Value = user.UserID;
                deleteCommand.ExecuteNonQuery();
            }

            sw.Stop();
            timing.Add(sw.ElapsedMilliseconds);
            System.Diagnostics.Debug.WriteLine(string.Format("Delete User by command count: {0} in {1}.", count, sw.ElapsedMilliseconds));
            connection.Close();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            int count = int.Parse(this.comboBox1.Text);
            List<long> timing = new List<long>();
            Test_DAC(count, timing);
            string title = "dac";
            ShowResult(count, timing, title);
        }

        private static void Test_DAC(int count, List<long> timing)
        {
            RaisingStudio.Data.Providers.DataContext dc = new RaisingStudio.Data.Providers.DataContext();
            dc.Connection.Open();
            System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
            sw.Start();
            for (int i = 0; i < count; i++)
            {
                SystemUser user = new SystemUser();
                user.UserID = i.ToString();
                user.UserName = string.Format("User{0}", i);
                user.FullName = string.Format("User full name: {0}", i);
                user.ChangingPassword = false;
                user.UserLocked = false;
                dc.Insert<SystemUser>(user);
            }
            sw.Stop();
            timing.Add(sw.ElapsedMilliseconds);
            System.Diagnostics.Debug.WriteLine(string.Format("Insert User by dac count: {0} in {1}.", count, sw.ElapsedMilliseconds));
            sw.Reset();

            sw.Start();
            IList<SystemUser> users = dc.QueryForList<SystemUser>();
            sw.Stop();
            timing.Add(sw.ElapsedMilliseconds);
            System.Diagnostics.Debug.WriteLine(string.Format("Query User by dac count: {0} in {1}.", count, sw.ElapsedMilliseconds));
            sw.Reset();

            sw.Start();
            foreach (SystemUser user in users)
            {
                user.Password = "new password";
                user.LastLogin = System.DateTime.Now;
                dc.Update<SystemUser>(user);
            }
            sw.Stop();
            timing.Add(sw.ElapsedMilliseconds);
            System.Diagnostics.Debug.WriteLine(string.Format("Update User by dac count: {0} in {1}.", count, sw.ElapsedMilliseconds));
            sw.Reset();

            sw.Start();
            foreach (SystemUser user in users)
            {
                //dc.Delete<SystemUser>(user);
                dc.Delete<SystemUser>(user.UserID);
            }
            sw.Stop();
            timing.Add(sw.ElapsedMilliseconds);
            System.Diagnostics.Debug.WriteLine(string.Format("Delete User by dac count: {0} in {1}.", count, sw.ElapsedMilliseconds));
            dc.Connection.Close();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            int count = int.Parse(this.comboBox1.Text);
            List<long> timing = new List<long>();
            Test_IBatis(count, timing);
            string title = "ibatis";
            ShowResult(count, timing, title);
        }

        private static void Test_IBatis(int count, List<long> timing)
        {
            Mapper.Instance().OpenConnection();
            System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
            sw.Start();
            for (int i = 0; i < count; i++)
            {
                SystemUser user = new SystemUser();
                user.UserID = i.ToString();
                user.UserName = string.Format("User{0}", i);
                user.FullName = string.Format("User full name: {0}", i);
                user.ChangingPassword = false;
                user.UserLocked = false;
                Mapper.Instance().Insert("InsertSystemUser", user);
            }
            sw.Stop();
            timing.Add(sw.ElapsedMilliseconds);
            System.Diagnostics.Debug.WriteLine(string.Format("Insert User by ibatis count: {0} in {1}.", count, sw.ElapsedMilliseconds));
            sw.Reset();

            sw.Start();
            IList<SystemUser> users = Mapper.Instance().QueryForList<SystemUser>("SelectSystemUser", null);

            sw.Stop();
            timing.Add(sw.ElapsedMilliseconds);
            System.Diagnostics.Debug.WriteLine(string.Format("Query User by ibatis count: {0} in {1}.", count, sw.ElapsedMilliseconds));
            sw.Reset();

            sw.Start();
            foreach (SystemUser user in users)
            {
                user.Password = "new password";
                user.LastLogin = System.DateTime.Now;
                Mapper.Instance().Update("UpdateSystemUser", user);
            }
            sw.Stop();
            timing.Add(sw.ElapsedMilliseconds);
            System.Diagnostics.Debug.WriteLine(string.Format("Update User by ibatis count: {0} in {1}.", count, sw.ElapsedMilliseconds));
            sw.Reset();

            sw.Start();
            foreach (SystemUser user in users)
            {
                Mapper.Instance().Delete("DeleteSystemUser", user.UserID);
            }
            sw.Stop();
            timing.Add(sw.ElapsedMilliseconds);
            System.Diagnostics.Debug.WriteLine(string.Format("Delete User by ibatis count: {0} in {1}.", count, sw.ElapsedMilliseconds));
            Mapper.Instance().CloseConnection();
        }

        private void button4_Click(object sender, EventArgs e)
        {
            List<List<long>> commndTimings = new List<List<long>>();
            List<List<long>> ibatisTimings = new List<List<long>>();
            List<List<long>> dacTimings = new List<List<long>>();
            for (int i = 1000; i <= 10000; i+=1000)
            {
                List<long> commandTiming = new List<long>();
                Test_Command(i, commandTiming);
                commndTimings.Add(commandTiming);

                List<long> ibatisTiming = new List<long>();
                Test_IBatis(i, ibatisTiming);
                ibatisTimings.Add(ibatisTiming);

                List<long> dacTiming = new List<long>();
                Test_DAC(i, dacTiming);
                dacTimings.Add(dacTiming);
            }
            StringBuilder messageBuilder = new StringBuilder();
            OutputTimingResult(commndTimings, 0, messageBuilder);
            OutputTimingResult(commndTimings, 1, messageBuilder);
            OutputTimingResult(commndTimings, 2, messageBuilder);
            OutputTimingResult(commndTimings, 3, messageBuilder);
            OutputTimingResult(ibatisTimings, 0, messageBuilder);
            OutputTimingResult(ibatisTimings, 1, messageBuilder);
            OutputTimingResult(ibatisTimings, 2, messageBuilder);
            OutputTimingResult(ibatisTimings, 3, messageBuilder);
            OutputTimingResult(dacTimings, 0, messageBuilder);
            OutputTimingResult(dacTimings, 1, messageBuilder);
            OutputTimingResult(dacTimings, 2, messageBuilder);
            OutputTimingResult(dacTimings, 3, messageBuilder);
            string title = "result";
            MessageBox.Show(messageBuilder.ToString(), title, MessageBoxButtons.OK);
        }

        private void OutputTimingResult(List<List<long>> timings, int index, StringBuilder messageBuilder)
        {
            foreach (List<long> timing in timings)
            {
                System.Console.Write(timing[index]);
                System.Console.Write("\t");

                messageBuilder.AppendFormat("{0}\t", timing[index]);
            }
            System.Console.WriteLine();
            messageBuilder.AppendLine();
        }

        private void button5_Click(object sender, EventArgs e)
        {
            (new Form2()).Show();
        }

        private void button6_Click(object sender, EventArgs e)
        {
            (new Form3()).Show();
        }
    }
}